package com.example.domain.repository;

import com.example.domain.entity.PriceList;
import com.example.domain.entity.enums.PriceListStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 价目表Repository
 * 
 * @author Means
 * @since 2025-10-27
 */
@Repository
public interface PriceListRepository extends JpaRepository<PriceList, String> {
    
    // ========== 基础查询 ==========
    
    /**
     * 查询未删除的价目表列表
     * 
     * @param isDeleted 是否删除
     * @return 价目表列表
     */
    List<PriceList> findByIsDeleted(Boolean isDeleted);
    
    /**
     * 根据状态查询未删除的价目表
     * 
     * @param status 价目表状态
     * @param isDeleted 是否删除
     * @return 价目表列表
     */
    List<PriceList> findByStatusAndIsDeleted(PriceListStatus status, Boolean isDeleted);
    
    // ========== 时间范围查询 ==========
    
    /**
     * 查询指定时间点生效的价目表（状态为ACTIVE且在有效期内）
     * 
     * @param status 价目表状态
     * @param currentTime 当前时间
     * @param isDeleted 是否删除
     * @return 价目表列表
     */
    @Query("SELECT p FROM PriceList p WHERE p.status = :status " +
           "AND p.effectiveFrom <= :currentTime " +
           "AND (p.effectiveTo IS NULL OR p.effectiveTo > :currentTime) " +
           "AND p.isDeleted = :isDeleted " +
           "ORDER BY p.effectiveFrom DESC")
    List<PriceList> findEffectivePriceLists(@Param("status") PriceListStatus status, 
                                           @Param("currentTime") LocalDateTime currentTime,
                                           @Param("isDeleted") Boolean isDeleted);
    
    /**
     * 查询当前生效的价目表（取第一个）
     * 
     * @param currentTime 当前时间
     * @return 价目表对象
     */
    @Query("SELECT p FROM PriceList p WHERE p.status = 'ACTIVE' " +
           "AND p.effectiveFrom <= :currentTime " +
           "AND (p.effectiveTo IS NULL OR p.effectiveTo > :currentTime) " +
           "AND p.isDeleted = false " +
           "ORDER BY p.effectiveFrom DESC")
    Optional<PriceList> findCurrentEffectivePriceList(@Param("currentTime") LocalDateTime currentTime);
    
    // ========== 分页查询 ==========
    
    /**
     * 分页查询未删除的价目表
     * 
     * @param isDeleted 是否删除
     * @param pageable 分页参数
     * @return 价目表分页结果
     */
    Page<PriceList> findByIsDeleted(Boolean isDeleted, Pageable pageable);
    
    /**
     * 根据状态分页查询未删除的价目表
     * 
     * @param status 价目表状态
     * @param isDeleted 是否删除
     * @param pageable 分页参数
     * @return 价目表分页结果
     */
    Page<PriceList> findByStatusAndIsDeleted(PriceListStatus status, Boolean isDeleted, Pageable pageable);
    
    // ========== 自定义查询 ==========
    
    /**
     * 根据ID查询未删除的价目表
     * 
     * @param id 价目表ID
     * @return 价目表对象
     */
    @Query("SELECT p FROM PriceList p WHERE p.id = :id AND p.isDeleted = false")
    Optional<PriceList> findByIdAndNotDeleted(@Param("id") String id);
    
    /**
     * 查询所有ACTIVE状态的未删除价目表，按生效时间倒序
     * 
     * @return 价目表列表
     */
    @Query("SELECT p FROM PriceList p WHERE p.status = 'ACTIVE' AND p.isDeleted = false ORDER BY p.effectiveFrom DESC")
    List<PriceList> findAllActivePriceLists();
    
    /**
     * 查询指定时间范围内生效的价目表
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param isDeleted 是否删除
     * @return 价目表列表
     */
    @Query("SELECT p FROM PriceList p WHERE " +
           "p.effectiveFrom <= :endTime " +
           "AND (p.effectiveTo IS NULL OR p.effectiveTo >= :startTime) " +
           "AND p.isDeleted = :isDeleted " +
           "ORDER BY p.effectiveFrom DESC")
    List<PriceList> findByEffectiveDateRange(@Param("startTime") LocalDateTime startTime,
                                             @Param("endTime") LocalDateTime endTime,
                                             @Param("isDeleted") Boolean isDeleted);
    
    /**
     * 软删除价目表
     *
     * @param id 价目表ID
     */
    @Modifying
    @Query("UPDATE PriceList p SET p.isDeleted = true, p.deletedAt = CURRENT_TIMESTAMP WHERE p.id = :id AND p.isDeleted = false")
    void softDelete(@Param("id") String id);
}

